package com.eorchis.module.news.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.eorchis.constants.RoleConstants;
import com.eorchis.constants.SystemConstants;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.IQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.OrderType;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.news.dao.INewsDao;
import com.eorchis.module.news.domain.News;
import com.eorchis.module.news.domain.NewsQueryBean;
import com.eorchis.module.news.domain.ReceiveUserQueryBean;
import com.eorchis.module.news.ui.commond.NewsQueryCommond;
import com.eorchis.module.news.ui.commond.NewsValidCommond;
import com.eorchis.module.remind.dao.IRemindDao;
import com.eorchis.module.remind.domain.RemindUser;
import com.eorchis.module.remind.ui.commond.RemindQueryCommond;
import com.eorchis.module.trainingclass.querybean.TrainingClassQueryBean;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: NewsDaoImpl<br>
 * Description: 消息管理数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author RongSL
 * @createDate 2016-05-06
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.news.dao.impl.NewsDaoImpl")
public class NewsDaoImpl extends HibernateAbstractBaseDao implements INewsDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	@Resource(name = "com.eorchis.module.remind.dao.impl.RemindDaoImpl")
	private IRemindDao remindDao;
	
	@Override
	public Class<? extends IBaseEntity> entityClass() {
		return News.class;
	}

	@Override
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		NewsQueryCommond newsQueryCommond = (NewsQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM News t");
		defaultQueryConditionBuilder.addCondition("t.newsId", CompareType.IN, newsQueryCommond.getSearchNewsIds());
		defaultQueryConditionBuilder.addCondition("t.sendLookState", CompareType.LIKE,newsQueryCommond.getSearchSendLookState());
		defaultQueryConditionBuilder.addCondition("t.newsValue", CompareType.LIKE,newsQueryCommond.getSearchNewsValue());
		defaultQueryConditionBuilder.addCondition("t.typeId", CompareType.LIKE,newsQueryCommond.getSearchTypeId());
	}
	/**
	 * 查询未选接收人列表
	 * @param queryCommond
	 * @param request
	 * @param response
	 * @param resultState
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2016-5-10
	 */
	@Override
	public List<ReceiveUserQueryBean> findReceiveUserList(
			NewsQueryCommond queryCommond) {
		Map<String, Object> params=new HashMap<String, Object>();
		StringBuffer hql=new StringBuffer("select c.userid as \"userId\",c.LOGIN_ID as \"loginId\",c.USER_NAME as \"userName\",c.DEPNAME as \"depName\"," +
				"c.role_name as \"roleName\",c.role_code as \"roleCode\" from (select t.userid,t.LOGIN_ID,t.USER_NAME,t.DEPNAME,wm_concat(distinct t.role_name) as role_name," +
				"wm_concat(distinct t.role_code) as role_code from (select u.userid    as userid,u.LOGIN_ID  as LOGIN_ID,u.USER_NAME as USER_NAME," +
				"d2.DEPNAME  as DEPNAME,d.DEPNAME   as SCOPESDEPNAME,r.role_name as role_name,r.role_code as role_code from UUM_SYSTEM_ROLE_SCOPES rs " +
				"inner join UUM_SYSTEM_ROLE r on rs.ROLE_ID = r.ROLE_ID inner join UUM_SYSTEM us on r.system_id = us.system_id inner join UUM_CONTROLSCOPE uc " +
				"on rs.CONTROLSCOPE_ID = uc.CONTROLSCOPE_ID inner join UUM_DEPARTMENT d on uc.DEPID = d.DEPID inner join UUM_USER u " +
				"on uc.USERID = u.USERID, UUM_DEPARTMENT_USER udu inner join UUM_USER u2  on udu.USERID = u2.USERID inner join UUM_DEPARTMENT d2 " +
				"on udu.DEPID = d2.DEPID where udu.USERID = u.USERID and us.system_id = :systemCode and us.active_state = :activeState " +
				"and rs.ACTIVE_STATE = :activeState and r.ACTIVE_STATE = :activeState and uc.ACTIVE_STATE = :activeState and d.ACTIVE_STATE = :activeState " +
				"and u.ACTIVE_STATE = :activeState and udu.ACTIVE_STATE = :activeState and u2.active_state = :activeState and d2.active_state = :activeState " +
				"group by t.userid, t.LOGIN_ID, t.USER_NAME, t.DEPNAME) c  where 1=1 ");
		params.put("systemCode",RoleConstants.XT_CODE);
		params.put("activeState",ReceiveUserQueryBean.IS_ACTIVE_Y);
		//登录号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginId())){
			hql.append(" and c.LOGIN_ID = :loginId ");
			params.put("loginId",queryCommond.getSearchLoginId());
		}
		//姓名
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())){
			hql.append(" and c.USER_NAME like :userName ");
			params.put("userName","%"+queryCommond.getSearchUserName().trim()+"%");
		}
		//单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())){
			hql.append(" and c.DEPNAME like :depName ");
			params.put("depName","%"+queryCommond.getSearchDepName().trim()+"%");
		}
		//角色
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchRoleCode())){
			hql.append(" and c.role_code like :roleCode ");
			params.put("roleCode","%"+queryCommond.getSearchRoleCode().trim()+"%");
		}
		//已选人员ids
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSelectedUserIds())){
			hql.append(" and c.userid in (:userIds) ");
			params.put("userIds",queryCommond.getSearchSelectedUserIds());
		}
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				hql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					hql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}else{
			hql.append(" order by c.userid asc ");
		}
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setResultObject(ReceiveUserQueryBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		
		List<ReceiveUserQueryBean> findList = new ArrayList<ReceiveUserQueryBean>();
		if(queryCommond.getPage()==-1){
			 findList = (List<ReceiveUserQueryBean>) this.findList(queryCondition);
		}else{
			 Long count = this.count(queryCondition);
			 int firstResult = queryCommond.calculate(count.longValue());
			 findList = (List<ReceiveUserQueryBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	/**
	 * 查询附件
	 * @param blobId
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2016-5-11
	 */
	@Override
	public List<ReceiveUserQueryBean> findBlobList(String blobId)
			throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		List<ReceiveUserQueryBean> resultList = null;
		if(PropertyUtil.objectNotEmpty(blobId)){
		sql.append("select bb.blob_id as \"blobId\"  , " +
					"bb.att_name   as  \"attName\" " +
					"from base_BLOB bb where  bb.blob_id =:blobId");
			params.put("blobId",blobId);
			IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
			
	
			conditionBuilder.setBaseQueryString(sql.toString());
			conditionBuilder.setQueryStringType(QueryStringType.SQL);
			conditionBuilder.setResultObject(ReceiveUserQueryBean.class);
		
			IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(params);
		
			resultList = new ArrayList<ReceiveUserQueryBean>();
			resultList = (List<ReceiveUserQueryBean>) this.findList(queryCondition);
		}
		return resultList;
	}
	/**
	 * 查询接收人ID及姓名
	 * @param commond
	 * @param request
	 * @param response
	 * @param resultState
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @date 2016-5-11
	 */
	@Override
	public NewsValidCommond findReceiveUser(NewsValidCommond commond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select group_concat(nu.user_id) as \"receiveUserIds\", group_concat(u.user_name) as \"receiveUserNames\","
				+ " bb.att_name as \"attachName\", bb.att_ext as \"attachType\",n.class_name as \"className\" "
				+ " from news n left join news_user_link nu on n.news_id = nu.news_id"
				+ " left join "+SystemConstants.SYSTEM_CODE+"UUM_USER u on nu.user_id = u.userid"
				+ " left join base_blob bb on n.attachment_id = bb.blob_id where n.news_id = :newsId"
				+ " group by bb.att_name, bb.att_ext,n.class_name ");
		params.put("newsId", commond.getNewsId());
		return (NewsValidCommond) this.executeFind(QueryStringType.SQL, sql.toString(), params, NewsValidCommond.class).get(0);
		
	}
	
	/**
	 * 查询委托部委管理员
	 * @Date 2016-05-13
	 * @author RongSL
	 * */
	@Override
	public List<RemindUser> findAdministrator(NewsQueryCommond commond)throws Exception{
		StringBuffer sql=new StringBuffer("" +
				"select u.userid    as \"userId\"," +	// 用户id 
				"u.LOGIN_ID  as \"loginID\",  " +					//登录号
				"u.USER_NAME as \"userName\",  " +		//用户姓名
				"d2.DEPNAME  as \"depName\",  " +			//所属单位
				"group_CONCAT(d.DEPNAME)  as \"scopesDepName\",  " +//管辖单位
				"group_CONCAT(r.role_name) as \"roleName\",  " +				//角色名称
				"group_CONCAT(r.role_code) as \"roleCode\" , " +					//角色编码
				" (select 1) as \"selectState\"  " +					//选取状态
				"from "+SystemConstants.SYSTEM_CODE+"UUM_SYSTEM_ROLE_SCOPES rs  " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_SYSTEM_ROLE r  on rs.ROLE_ID = r.ROLE_ID " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_SYSTEM us on r.system_id = us.system_id  " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_CONTROLSCOPE uc on rs.CONTROLSCOPE_ID = uc.CONTROLSCOPE_ID " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_DEPARTMENT d on uc.DEPID = d.DEPID " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_USER u on uc.USERID = u.USERID, "+SystemConstants.SYSTEM_CODE+"UUM_DEPARTMENT_USER udu " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_USER u2 on udu.USERID = u2.USERID " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_DEPARTMENT d2 on udu.DEPID = d2.DEPID " +
				"where udu.USERID = u.USERID and us.system_id =:systemCode  " +
				"and us.active_state =:activeState and rs.active_state =:activeState and r.active_state =:activeState  " +
				"and uc.active_state =:activeState and d.active_state =:activeState " +
				"and u.active_state =:activeState and udu.active_state =:activeState " +
				"and u2.active_state =:activeState and d2.active_state =:activeState ");
		StringBuffer sql2=new StringBuffer("" +
				"select u.userid    as \"userId\"," +	// 用户id 
				"u.LOGIN_ID  as \"loginID\",  " +					//登录号
				"u.USER_NAME as \"userName\",  " +		//用户姓名
				"d2.DEPNAME  as \"depName\",  " +			//所属单位
				"group_CONCAT(d.DEPNAME)  as \"scopesDepName\",  " +//管辖单位
				"group_CONCAT(r.role_name) as \"roleName\",  " +				//角色名称
				"group_CONCAT(r.role_code) as \"roleCode\" , " +					//角色编码
				" (select 2) as \"selectState\"  " +					//选取状态
				"from "+SystemConstants.SYSTEM_CODE+"UUM_SYSTEM_ROLE_SCOPES rs  " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_SYSTEM_ROLE r  on rs.ROLE_ID = r.ROLE_ID " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_SYSTEM us on r.system_id = us.system_id  " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_CONTROLSCOPE uc on rs.CONTROLSCOPE_ID = uc.CONTROLSCOPE_ID " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_DEPARTMENT d on uc.DEPID = d.DEPID " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_USER u on uc.USERID = u.USERID, "+SystemConstants.SYSTEM_CODE+"UUM_DEPARTMENT_USER udu " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_USER u2 on udu.USERID = u2.USERID " +
				"inner join "+SystemConstants.SYSTEM_CODE+"UUM_DEPARTMENT d2 on udu.DEPID = d2.DEPID " +
				"where udu.USERID = u.USERID and us.system_id =:systemCode  " +
				"and us.active_state =:activeState and rs.ACTIVE_STATE =:activeState and r.ACTIVE_STATE =:activeState  " +
				"and uc.ACTIVE_STATE =:activeState and d.ACTIVE_STATE =:activeState " +
				"and u.ACTIVE_STATE =:activeState and udu.ACTIVE_STATE =:activeState " +
				"and u2.active_state =:activeState and d2.active_state =:activeState ");
		Map<String,Object> params=new HashMap<String,Object>();
		
		if(PropertyUtil.objectNotEmpty(commond.getSearchUserName())){
			sql.append(" and u.USER_NAME like :userName ");
			sql2.append(" and u.USER_NAME like :userName ");
			params.put("userName", "%"+commond.getSearchUserName()+"%");
		}
		if(PropertyUtil.objectNotEmpty(commond.getSearchDepName())){
			sql.append(" and d2.depname like :depName ");
			sql2.append(" and d2.depname like :depName ");
			params.put("depName","%"+commond.getSearchDepName()+"%");
		}
		if(PropertyUtil.objectNotEmpty(commond.getSearchLoginId())){
			sql.append(" and u.LOGIN_ID like :loginId ");
			sql2.append(" and u.LOGIN_ID like :loginId ");
			params.put("loginId","%"+ commond.getSearchLoginId()+"%");
		}
		if(PropertyUtil.objectNotEmpty(commond.getSearchClassIds()) && PropertyUtil.objectNotEmpty(commond.getSearchClassIds()[0])){
			RemindQueryCommond c = new RemindQueryCommond();
			c.setSearchClassIds(commond.getSearchClassIds());
			c.setSearchPublishState(1);
			List<TrainingClassQueryBean> l=remindDao.findNClass(c);
			String DF[]=new String[l.size()];
			Iterator<TrainingClassQueryBean> ite=l.iterator();
			while(ite.hasNext()){
				String DeptName=ite.next().getTrainingDeptName();
				if(PropertyUtil.objectNotEmpty(DeptName)){
					DF=DeptName.split(",");
				}
			}
			sql.append(" and ((d.DEPNAME in (select tc.ministrie_name from training_class tc where tc.class_id in (:classID) " +
					"union  select tc.joint_ministrie_name name from training_class tc where tc.class_id in (:classID) and tc.joint_ministrie_name is not null) " +
					"and r.role_code = 'wtbwbb_bw_admin') or r.role_code = 'wtbwbb_zzb_admin' ");
			sql2.append(" and ((d.DEPNAME in (select tc.ministrie_name from training_class tc where tc.class_id in (:classID) " +
					"union  select tc.joint_ministrie_name name from training_class tc where tc.class_id in (:classID) and tc.joint_ministrie_name is not null) " +
					"and r.role_code = 'wtbwbb_bw_admin') or r.role_code = 'wtbwbb_zzb_admin' ");
			params.put("classID", commond.getSearchClassIds());
			if(null!=DF&&null!=DF[0]){
				sql.append(" or (d.DEPNAME in (:dfAdmini) and r.role_code='wtbwbb_dw_admin') ");
				sql2.append(" or (d.DEPNAME in (:dfAdmini) and r.role_code='wtbwbb_dw_admin') ");
				params.put("dfAdmini", DF);
			}
			sql.append(" )");
			sql2.append(" )");
		}
		
//		if(PropertyUtil.objectNotEmpty(commond.getSearchSelectState())){
//			if(commond.getSearchSelectState()==1){//已选列表
				if(PropertyUtil.objectNotEmpty(commond.getSearchSelectedUserIds()) && PropertyUtil.objectNotEmpty(commond.getSearchSelectedUserIds()[0])){
					sql.append(" and u.userid  in ( :userids) ");
					params.put("userids", commond.getSearchSelectedUserIds());
				}else{
					sql.append(" and u.userid not in ( '' ) ");
				}
//			}else{//待选列表
				if(PropertyUtil.objectNotEmpty(commond.getSearchSelectedUserIds()) && PropertyUtil.objectNotEmpty(commond.getSearchSelectedUserIds()[0])){
					sql2.append(" and u.userid not in ( :userids ) ");
					params.put("userids", commond.getSearchSelectedUserIds());
				}
				if(PropertyUtil.objectNotEmpty(commond.getSearchUserId())){
					sql2.append(" and u.userid <> (:currentUserId)");
					params.put("currentUserId", commond.getSearchUserId());
				}
//			}
//		}
		sql.append(" group by u.userid,u.LOGIN_ID,u.USER_NAME,d2.DEPNAME ");
		sql2.append(" group by u.userid,u.LOGIN_ID,u.USER_NAME,d2.DEPNAME ");
		
		StringBuffer sql3=new StringBuffer("select * from (( "+sql+" ) union ( "+sql2+" )) t where 1=1 ");
		
		if(PropertyUtil.objectNotEmpty(commond.getSearchRoleCode())){
			sql3.append(" \"roleName\" like :roleName ");
			params.put("roleName", "%"+commond.getSearchRoleCode().trim()+"%");
		}
		
		sql3.append(" order by \"selectState\"  ,\"userName\" ");
		params.put("systemCode",RoleConstants.XCH_CODE);
		params.put("activeState",ReceiveUserQueryBean.IS_ACTIVE_Y);
		System.out.println("===========================ReceiveUserQueryBean.XCH_CODE=================="+RoleConstants.XT_CODE);
		return (List<RemindUser>)this.executeFind(QueryStringType.SQL, sql3.toString(), params,RemindUser.class);
	}

	/**
	 * 查询消息列表
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-6-21
	 */
	public List<NewsQueryBean> findNewsList(NewsQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("select t.news_id as \"newsId\", t.news_title as \"newsTitle\","
				+ " t.news_value as \"newsValue\", t.newsType as \"newsType\", t.newsTypeCode as \"newsTypeCode\",t.sendTime as \"sendTime\","
//				+ " t.receiveUsers as \"receiveUsers\", "
				+ "t.readNum as \"readNum\",t.receiveNumAll as \"receiveNumAll\", t.isNewReply as \"isNewReply\""
				+ " from "
				+ "(select n.news_id, n.news_title, n.news_value, bd.data_name as newsType, bd.data_code as newsTypeCode,n.create_date as sendTime,"
//				+ " concat(group_concat( case when u.user_name is null then '' else concat(u.user_name,'(',d.depname,')') end order by d.depid ),'') as receiveUsers,"
				+ " sum(case nu.read_state when 1 then 1 else 0 end) as readNum,count(nu.news_user_id) as receiveNumAll,"
				+ "(select (case when count(r.reply_id) > 0 then 1 else 0 end) from reply r where r.news_user_id in "
				+ "(select znu.news_user_id from news_user_link znu where znu.news_id = n.news_id) ");
				if(!PropertyUtil.objectNotEmpty(queryCommond.getRoleCode())){
					sql.append(" and r.user_id not in (select udu.userid from "+SystemConstants.SYSTEM_CODE+"uum_department_user udu "
							+ "LEFT JOIN "+SystemConstants.SYSTEM_CODE+"UUM_SYS_DEP_SYSROLE_GRP_VIEW usds ON udu.userid = usds.USERID "
							+ "where udu.depid=:depid  AND usds.userid IS NOT NULL)  ");
				}else{
					sql.append(" and r.user_id<>:userId ");
					values.put("userId", queryCommond.getSearchUserId());
				}
				sql.append(" and r.read_state = 2) as isNewReply"
				+ " from news n"
				+ " left join news_user_link nu on n.news_id = nu.news_id"
//				+ " left join "+SystemConstants.SYSTEM_CODE+"uum_user u on nu.user_id = u.userid"
//				+ " left join "+SystemConstants.SYSTEM_CODE+"uum_department_user du on u.userid = du.userid"
//				+ " left join "+SystemConstants.SYSTEM_CODE+"uum_department d on du.depid = d.depid"
				+ " left join base_basedata bd on n.news_code = bd.data_code where n.send_look_state = :sendLookState ");
				if(!PropertyUtil.objectNotEmpty(queryCommond.getRoleCode())){
					sql.append(" and n.send_user_id in (select udu.userid from "+SystemConstants.SYSTEM_CODE+"uum_department_user udu "
							+ "left join "+SystemConstants.SYSTEM_CODE+"UUM_SYS_DEP_SYSROLE_GRP_VIEW usds on udu.userid=usds.USERID "
							+ "where udu.depid=:depid and usds.userid is not null )");
				}
		values.put("sendLookState", News.IS_Y);
//		values.put("userId", queryCommond.getSearchUserId());
		values.put("depid", queryCommond.getDeptID());
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsValue())) {
			sql.append(" and n.news_value like :newsValue");
			values.put("newsValue", "%" + queryCommond.getSearchNewsValue().replace(" ", "") + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsCode())) {
			sql.append(" and n.news_code = :newsCode");
			values.put("newsCode", queryCommond.getSearchNewsCode());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			sql.append(" and to_date(to_char(n.create_date,'yyyy-MM-dd'), 'yyyy-MM-dd') >= to_date(:beginDate, 'yyyy-MM-dd')");
			values.put("beginDate", queryCommond.getSearchBeginDate());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())) {
			sql.append(" and to_date(to_char(n.create_date,'yyyy-MM-dd'), 'yyyy-MM-dd') <= to_date(:endDate, 'yyyy-MM-dd')");
			values.put("endDate", queryCommond.getSearchEndDate());
		}
		sql.append(" group by n.news_id, n.news_title, n.news_value, bd.data_name, bd.data_code, n.create_date) t");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchReceiveUser())) {
			sql.append(" where t.receiveUsers like :userName");
			values.put("userName", "%" + queryCommond.getSearchReceiveUser().replace(" ", "") + "%");
		}
		IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
		conditionBuilder.setBaseQueryString(sql.toString());
		conditionBuilder.setQueryStringType(QueryStringType.SQL);
		conditionBuilder.setResultObject(NewsQueryBean.class);
		
		// 增加排序
		if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfos())) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			conditionBuilder.addSort(sortInfoBean);
		} else if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfo())) {
			List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
			for (SortInfoBean bean : sortInfoList) {
				conditionBuilder.addSort(bean);
			}
		} else {
			conditionBuilder.addSort("t.sendTime", OrderType.DESC);
			conditionBuilder.addSort("t.news_id", OrderType.DESC);
		}
		
		IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<NewsQueryBean> resultList = new ArrayList<NewsQueryBean>();
		if (queryCommond.getPage() == -1) {
			resultList = (List<NewsQueryBean>) this.findList(queryCondition);
		} else {
			Long count = this.count(queryCondition);
			int firstResult = queryCommond.calculate(count);
			resultList = (List<NewsQueryBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	
	
	
	
	/**
	 * 未读消息数
	 * @return
	 * @throws Exception
	 * @author GUOLEI
	 * @createDate 2016-6-21
	 */
	@Override
	public Integer receiveNewsCount(NewsValidCommond validCommond) throws Exception {
		Integer num=0;
		
		StringBuffer sql = new StringBuffer("select  count(*) as \"receiveNum\"  " +
					"from  NEWS_USER_LINK  nul  where   nul.READ_STATE=:readState  and   nul.user_id=:userId  ");
		Map<String, Object> values = new HashMap<String, Object>();
		values.put("readState", News.IS_N);
		values.put("userId", validCommond.getUserId().trim());
		
		IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
		conditionBuilder.setBaseQueryString(sql.toString());
		conditionBuilder.setQueryStringType(QueryStringType.SQL);
		conditionBuilder.setResultObject(NewsQueryBean.class);

		IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<NewsQueryBean>  resultList = (List<NewsQueryBean>) this.findList(queryCondition);
		
		if(PropertyUtil.objectNotEmpty(resultList)){
			num=resultList.get(0).getReceiveNum();
		}
		
		return num;
	}
	
	

	/**
	 * 接收消息列表
	 * @return
	 * @throws Exception
	 * @author GUOLEI
	 * @createDate 2016-6-21
	 */
	@Override
	public List<NewsQueryBean> replyNewsUserList(NewsQueryCommond queryCommond) throws Exception {
		List<NewsQueryBean> resultList =null;
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserId())){
			String isNewReplySql = "(select (case when count(r.reply_id) > 0 then 1 else 0 end) from reply r "
					+ "where r.news_user_id =nul.news_user_id and r.user_id <> :userId and r.read_state = 2) as \"isNewReply\"";
			StringBuffer sql = new StringBuffer("select  n.NEWS_ID as  \"newsId\"  ," +
														" n.news_Title  as \"newsTitle\"," +
														" bb.data_name as \"newsType\" ," +
														" bu.user_name as \"userName\"," +
														" nul.create_date \"sendTime\"," +
														" nul.read_state \"readState\"," +
														" nul.REPLY_STATE \"erplyState\"," +
														" nul.NEWS_USER_ID \"newsUserLink\"," +
														isNewReplySql +
												" from  NEWS_USER_LINK   nul   " +
												" left  join  NEWS   n  " +
													" on  nul.news_id =   n.news_id  " +
												" left  join   BASE_BASEDATA  bb  " +
													" on bb.data_code = n.news_code " +
												" left join   "+SystemConstants.SYSTEM_CODE+"UUM_USER bu   " +
													" on  bu.userid =  n.send_user_id   " +
												" where  1=1 ");
//												if(PropertyUtil.objectNotEmpty(queryCommond.getRoleCode())&&!queryCommond.getRoleCode().equals(RoleConstants.ROLE_CODE_ONE_ADMIN)){
													sql.append("and nul.user_id=:userId  ");
//												}
												sql.append(" and  nul.ACTIVE_STATE=:activeState and n.news_id is not null " +
													" and nul.RECEIVE_LOOK_STATE=:sendLookState  ");
			Map<String, Object> values = new HashMap<String, Object>();
			
			//接收人ID
			values.put("userId", queryCommond.getSearchUserId());
			//活动状态
			values.put("activeState", News.IS_Y);
			//显示状态
			values.put("sendLookState", News.IS_Y);
			//消息标题
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsTitle())) {
				sql.append(" and n.news_title like :newsTitle");
				values.put("newsTitle", "%" + queryCommond.getSearchNewsTitle().replace(" ", "") + "%");
			}
			//消息类型
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsCode())) {
				sql.append(" and n.news_code = :newsCode");
				values.put("newsCode", queryCommond.getSearchNewsCode());
			}
			//消息发送人
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
				sql.append(" and bu.user_name like :userName");
				values.put("userName", "%" + queryCommond.getSearchUserName().replace(" ", "") + "%");
			}
			//接收消息开始时间
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
				sql.append(" and to_date(to_char(nul.create_date,'yyyy-MM-dd'), 'yyyy-MM-dd') >= to_date(:beginDate, 'yyyy-MM-dd')");
				values.put("beginDate", queryCommond.getSearchBeginDate());
			}
			//接收消息结束时间
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())) {
				sql.append(" and to_date(to_char(nul.create_date,'yyyy-MM-dd'), 'yyyy-MM-dd') <= to_date(:endDate, 'yyyy-MM-dd')");
				values.put("endDate", queryCommond.getSearchEndDate());
			}
			//是否回复
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchReadState())) {
				sql.append(" and nul.read_state = :readState");
				values.put("readState", queryCommond.getSearchReadState());
			}
			//读取状态
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchErplyState())) {
				sql.append(" and nul.REPLY_STATE = :replyState");
				values.put("replyState", queryCommond.getSearchErplyState());
			}
			
			IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
			conditionBuilder.setBaseQueryString(sql.toString());
			conditionBuilder.setQueryStringType(QueryStringType.SQL);
			conditionBuilder.setResultObject(NewsQueryBean.class);
			
			// 增加排序
			if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfos())) {
				SortInfoBean sortInfoBean = queryCommond.getSortInfos();
				if(sortInfoBean.getProperty().equals("sendTime")){
					sortInfoBean.setProperty("nul.create_date");
				}
				conditionBuilder.addSort(sortInfoBean);
			} else if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfo())) {
				List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
				for (SortInfoBean bean : sortInfoList) {
					conditionBuilder.addSort(bean);
				}
			} else {
				conditionBuilder.addSort("nul.create_date", OrderType.DESC);
			}
			
			IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(values);
			
			if (queryCommond.getPage() == -1) {
				resultList = (List<NewsQueryBean>) this.findList(queryCondition);
			} else {
				IConditionBuilder conditionBuilderForCount = new DefaultQueryConditionBuilder();
				conditionBuilderForCount.setBaseQueryString(sql.toString().replace(isNewReplySql, ""));
				conditionBuilderForCount.setQueryStringType(QueryStringType.SQL);
				IQueryCondition queryConditionForCount = conditionBuilderForCount.buliderQueryCondition();
				queryConditionForCount.setParameters(values);
				Long count = this.count(queryConditionForCount);
				int firstResult = queryCommond.calculate(count);
				resultList = (List<NewsQueryBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
			}
		}
		return resultList;
	}
	
	/**
	 * 接收消息列表（一级管理员）
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2017-12-11
	 */
	@Override
	public List<NewsQueryBean> replyNewsUserListByOne(NewsQueryCommond queryCommond) throws Exception {
		List<NewsQueryBean> resultList =null;
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserId())){
			String isNewReplySql = "(select (case when count(r.reply_id) > 0 then 1 else 0 end) from reply r where r.news_user_id in " +
					"(select znu.news_user_id from news_user_link znu where znu.news_id = n.news_id and znu.user_id = :userId)" +
					" and r.user_id <> :userId and r.read_state = 2) as \"isNewReply\"";
			StringBuffer sql = new StringBuffer("select  n.NEWS_ID as  \"newsId\"  ," +
					" n.news_Title  as \"newsTitle\"," +
					" bb.data_name as \"newsType\" ," +
					" bu.user_name as \"userName\"," +
					" nul.create_date \"sendTime\"," +
					" nul.read_state \"readState\"," +
					" nul.REPLY_STATE \"erplyState\"," +
					" nul.NEWS_USER_ID \"newsUserLink\"," +
					isNewReplySql +
					" from  NEWS_USER_LINK   nul   " +
					" left  join  NEWS   n  " +
					" on  nul.news_id =   n.news_id  " +
					" left  join   BASE_BASEDATA  bb  " +
					" on bb.data_code = n.news_code " +
					" left join   "+SystemConstants.SYSTEM_CODE+"UUM_USER bu   " +
					" on  bu.userid =  n.send_user_id   " +
					" where   " +
					" nul.user_id=:userId  " +
					" and  nul.ACTIVE_STATE=:activeState " +
					" and nul.RECEIVE_LOOK_STATE=:sendLookState  ");
			Map<String, Object> values = new HashMap<String, Object>();
			
			//接收人ID
			values.put("userId", queryCommond.getSearchUserId());
			//活动状态
			values.put("activeState", News.IS_Y);
			//显示状态
			values.put("sendLookState", News.IS_Y);
			//消息标题
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsTitle())) {
				sql.append(" and n.news_value like :newsTitle");
				values.put("newsTitle", "%" + queryCommond.getSearchNewsTitle().replace(" ", "") + "%");
			}
			//消息类型
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsCode())) {
				sql.append(" and n.news_code = :newsCode");
				values.put("newsCode", queryCommond.getSearchNewsCode());
			}
			//消息发送人
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
				sql.append(" and bu.user_name like :userName");
				values.put("userName", "%" + queryCommond.getSearchUserName().replace(" ", "") + "%");
			}
			//接收消息开始时间
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
				sql.append(" and to_date(to_char(nul.create_date,'yyyy-MM-dd'), 'yyyy-MM-dd') >= to_date(:beginDate, 'yyyy-MM-dd')");
				values.put("beginDate", queryCommond.getSearchBeginDate());
			}
			//接收消息结束时间
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())) {
				sql.append(" and to_date(to_char(nul.create_date,'yyyy-MM-dd'), 'yyyy-MM-dd') <= to_date(:endDate, 'yyyy-MM-dd')");
				values.put("endDate", queryCommond.getSearchEndDate());
			}
			//是否回复
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchReadState())) {
				sql.append(" and nul.read_state = :readState");
				values.put("readState", queryCommond.getSearchReadState());
			}
			//读取状态
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchErplyState())) {
				sql.append(" and nul.REPLY_STATE = :replyState");
				values.put("replyState", queryCommond.getSearchErplyState());
			}
			
			IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
			conditionBuilder.setBaseQueryString(sql.toString());
			conditionBuilder.setQueryStringType(QueryStringType.SQL);
			conditionBuilder.setResultObject(NewsQueryBean.class);
			
			// 增加排序
			if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfos())) {
				SortInfoBean sortInfoBean = queryCommond.getSortInfos();
				if(sortInfoBean.getProperty().equals("sendTime")){
					sortInfoBean.setProperty("nul.create_date");
				}
				conditionBuilder.addSort(sortInfoBean);
			} else if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfo())) {
				List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
				for (SortInfoBean bean : sortInfoList) {
					conditionBuilder.addSort(bean);
				}
			} else {
				conditionBuilder.addSort("nul.create_date", OrderType.DESC);
			}
			
			IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(values);
			
			if (queryCommond.getPage() == -1) {
				resultList = (List<NewsQueryBean>) this.findList(queryCondition);
			} else {
				IConditionBuilder conditionBuilderForCount = new DefaultQueryConditionBuilder();
				conditionBuilderForCount.setBaseQueryString(sql.toString().replace(isNewReplySql, ""));
				conditionBuilderForCount.setQueryStringType(QueryStringType.SQL);
				IQueryCondition queryConditionForCount = conditionBuilderForCount.buliderQueryCondition();
				queryConditionForCount.setParameters(values);
				Long count = this.count(queryConditionForCount);
				int firstResult = queryCommond.calculate(count);
				resultList = (List<NewsQueryBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
			}
		}
		return resultList;
	}
	
	
	/**
	 * 查询回复消息人员列表
	 * @return
	 * @throws Exception
	 * @author GUOLEI
	 * @createDate 2016-6-21
	 */
	@Override
	public List<NewsQueryBean> findReplyUserList(NewsQueryCommond queryCommond) throws Exception {
		List<NewsQueryBean> resultList = null;
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsId())){
			StringBuffer sql = new StringBuffer("select t.* from (select nul.news_user_id as \"newsUserLink\" ,u.userid    as \"userId\"," +	// 用户id 
				"u.LOGIN_ID  as \"loginId\",  " +					//登录号
				"concat(u.USER_NAME,'(',ud.DEPNAME,')') as \"userName\",  " +		//用户姓名
//				"concat(group_CONCAT(r.role_name)) as \"roleName\",   " +				//角色名称-
				"(select (case when count(r.reply_id) > 0 then 1 else 0 end)" +
				" from reply r where r.news_user_id = nul.news_user_id and r.user_id <> :userId and r.read_state = :readStateN) as \"isNewReply\"," + //是否有新回复
				"(select max(r.create_date)" +
				" from reply r where r.news_user_id = nul.news_user_id and r.user_id <> :userId and r.read_state = :readStateN) as \"replyTime\"," + //是否有新回复
				"nul.read_state as \"readState\"," +	//读取状态
				"nul.receive_time as \"receiveTime\" " +	//接收时间
				"from NEWS_USER_LINK nul left JOIN "+SystemConstants.SYSTEM_CODE+"UUM_USER u ON nul.user_id = u.USERID "
				+ "left join "+SystemConstants.SYSTEM_CODE+"uum_department_user udu on u.USERID=udu.userid "
				+ "left join "+SystemConstants.SYSTEM_CODE+"uum_department ud on udu.depid=ud.depid " +
				"where  nul.news_id=:newsId ");
			//	"and nul.READ_STATE =:replyState ");
				
			
			Map<String, Object> values = new HashMap<String, Object>();
			
			
			values.put("userId", queryCommond.getSearchUserId());
//			values.put("systemCode",RoleConstants.XCH_CODE);
			
			//回复读取状态
			values.put("readStateN", News.IS_N);
			//活动状态
//			values.put("activeState", News.IS_Y);
			//显示状态
			/*values.put("sendLookState", News.IS_Y);*/
			//消息ID
			values.put("newsId", queryCommond.getSearchNewsId().replace(" ", "") );
			//人员登录号
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginId())) {
				sql.append(" and u.login_id like :loginId ");
				values.put("loginId","%"+queryCommond.getSearchLoginId().replace(" ", "")+"%");
			}
			//人员姓名 
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
				sql.append(" and u.user_name like :userName ");
				values.put("userName","%" + queryCommond.getSearchUserName().replace(" ", "")+"%");
			}
			//单位名称
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
				sql.append(" and d2.DEPNAME like :depName ");
				values.put("depName","%" + queryCommond.getSearchDepName().replace(" ", "")+"%");
			}
			//角色编号
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRoleCode())) {
				sql.append(" and r.role_code = :roleCode ");
				values.put("roleCode", queryCommond.getSearchRoleCode());
			}
			//读取状态
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchReadState())) {
				sql.append(" and nul.read_state = :readState ");
				values.put("readState", queryCommond.getSearchReadState());
			}
			sql.append(" ) t order by isNewReply desc, replyTime desc,receiveTime desc ");
			IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
			conditionBuilder.setBaseQueryString(sql.toString());
			conditionBuilder.setQueryStringType(QueryStringType.SQL);
			conditionBuilder.setResultObject(NewsQueryBean.class);
			
			// 增加排序
			if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfos())) {
				SortInfoBean sortInfoBean = queryCommond.getSortInfos();
				conditionBuilder.addSort(sortInfoBean);
			} else if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfo())) {
				List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
				for (SortInfoBean bean : sortInfoList) {
					conditionBuilder.addSort(bean);
				}
			} else {
				//conditionBuilder.addSort("r.CREATE_DATE", OrderType.DESC);
			}
			
			IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(values);
		
			if (queryCommond.getPage() == -1) {
				resultList = (List<NewsQueryBean>) this.findList(queryCondition);
			} else {
		 		Long	count=(long) this.findList(queryCondition).size();
		 		int firstResult = queryCommond.calculate(count);
				resultList = (List<NewsQueryBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
			}
		}
		return resultList;
	}
	
	
	
	/**
	 * 查询回复消息人员列表
	 * @return
	 * @throws Exception
	 * @author GUOLEI
	 * @createDate 2016-6-21
	 */
	public Integer findReplyUserCount(NewsQueryCommond queryCommond) throws Exception {
		List<NewsQueryBean> resultList = null;
		Integer num=0;
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsId())){
			StringBuffer sql = new StringBuffer("select nvl(count(distinct(u.userid)),0)    as \"readNum\" " +	// 用户id 
				"from UUM_SYSTEM_ROLE_SCOPES@Uum_Link rs  " +
				"inner join UUM_SYSTEM_ROLE@Uum_Link r  on rs.ROLE_ID = r.ROLE_ID " +
				"inner join UUM_SYSTEM@Uum_Link us on r.system_id = us.system_id  " +
				"inner join UUM_CONTROLSCOPE@Uum_Link uc on rs.CONTROLSCOPE_ID = uc.CONTROLSCOPE_ID " +
				"inner join UUM_DEPARTMENT@Uum_Link d on uc.DEPID = d.DEPID " +
				"inner join UUM_USER@Uum_Link u on uc.USERID = u.USERID, UUM_DEPARTMENT_USER@Uum_Link udu " +
				"inner join UUM_USER@Uum_Link u2 on udu.USERID = u2.USERID " +
				"inner join UUM_DEPARTMENT@Uum_Link d2 on udu.DEPID = d2.DEPID , " +
				" NEWS_USER_LINK nul  " +
				"where udu.USERID = u.USERID and us.system_id =:systemCode  " +
				"and us.active_state =:activeState and rs.ACTIVE_STATE =:activeState and r.ACTIVE_STATE =:activeState  " +
				"and uc.ACTIVE_STATE =:activeState and d.ACTIVE_STATE =:activeState " +
				"and u.ACTIVE_STATE =:activeState and udu.ACTIVE_STATE =:activeState " +
				"and u2.active_state =:activeState and d2.active_state =:activeState " +
				"and nul.user_id  =   u.userid  " +
				"and nul.news_id=:newsId " +
				"and nul.READ_STATE =:replyState ");
				
			
			Map<String, Object> values = new HashMap<String, Object>();
			
			
			values.put("systemCode",RoleConstants.XT_CODE);
			
			//回复状态
			values.put("replyState", News.IS_Y);
			//活动状态
			values.put("activeState", News.IS_Y);
			//显示状态
			values.put("sendLookState", News.IS_Y);
			//消息ID
			values.put("newsId", queryCommond.getSearchNewsId().replace(" ", "") );
			//人员登录号
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginId())) {
				sql.append(" and u.login_id like :loginId ");
				values.put("loginId","%"+queryCommond.getSearchLoginId().replace(" ", "")+"%");
			}
			//人员姓名 
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
				sql.append(" and u.user_name like :userName ");
				values.put("userName","%" + queryCommond.getSearchUserName().replace(" ", "")+"%");
			}
			//单位名称
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
				sql.append(" and d2.DEPNAME like :depName ");
				values.put("depName","%" + queryCommond.getSearchDepName().replace(" ", "")+"%");
			}
			//角色编号
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRoleCode())) {
				sql.append(" and r.role_code = :roleCode ");
				values.put("roleCode", queryCommond.getSearchRoleCode());
			}
			sql.append(" group by to_char(u.userid) ");
			
			IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
			conditionBuilder.setBaseQueryString(sql.toString());
			conditionBuilder.setQueryStringType(QueryStringType.SQL);
			conditionBuilder.setResultObject(NewsQueryBean.class);
			
			IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(values);

				resultList = (List<NewsQueryBean>) this.findList(queryCondition);
				
				
		}
		if(PropertyUtil.objectNotEmpty(resultList)){
			num=resultList.get(0).getReadNum();
		}
		return num;
	}
	
	
	
	/**
	 * 回复消息列表
	 * @return
	 * @throws Exception
	 * @author GUOLEI
	 * @createDate 2016-6-21
	 */
	@Override
	public List<NewsQueryBean> findReplyNewsList(NewsQueryCommond queryCommond) throws Exception {
		List<NewsQueryBean> resultList = null;
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNewsId())){
			StringBuffer sql = new StringBuffer("select  r.reply_value as \"replyValue\"," +
						" r.create_date   as \"sendTime\", " +
						" concat(bu.user_name,'(',d2.DEPNAME,')') as  \"userName\",r.reply_id as \"replyId\" " +
					" from  REPLY r  " +
						" left join    NEWS_USER_LINK nul    on  nul.news_user_id=r.news_user_id   " +
						" left  join   NEWS n on n.news_id=nul.news_id  " +
						" left join  "+SystemConstants.SYSTEM_CODE+"UUM_USER  bu   on bu.userid=r.user_id left join "+SystemConstants.SYSTEM_CODE+"UUM_DEPARTMENT_USER udu "
						+ "on udu.USERID = bu.USERID left join "+SystemConstants.SYSTEM_CODE+"UUM_DEPARTMENT d2  on udu.DEPID = d2.DEPID " +
					" where   r.ACTIVE_STATE=:activeState   " +
						"and  nul.NEWS_USER_ID=:newsUserId ");
			Map<String, Object> values = new HashMap<String, Object>();
			

			//活动状态
			values.put("activeState", News.IS_Y);
			//消息ID
			values.put("newsUserId", queryCommond.getSearchNewsUserLink().replace(" ", "") );
			//人员ID
			values.put("userId", queryCommond.getSearchUserId().replace(" ", "") );
			
			
			IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
			conditionBuilder.setBaseQueryString(sql.toString());
			conditionBuilder.setQueryStringType(QueryStringType.SQL);
			conditionBuilder.setResultObject(NewsQueryBean.class);
			
			// 增加排序
			if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfos())) {
				SortInfoBean sortInfoBean = queryCommond.getSortInfos();
				conditionBuilder.addSort(sortInfoBean);
			} else if (PropertyUtil.objectNotEmpty(queryCommond.getSortInfo())) {
				List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
				for (SortInfoBean bean : sortInfoList) {
					conditionBuilder.addSort(bean);
				}
			} else {
				conditionBuilder.addSort("r.CREATE_DATE", OrderType.DESC);
			}
			
			IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(values);
		
			if (queryCommond.getPage() == -1) {
				resultList = (List<NewsQueryBean>) this.findList(queryCondition);
			} else {
				Long count = this.count(queryCondition);
				int firstResult = queryCommond.calculate(count);
				resultList = (List<NewsQueryBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
			}
		}
		return resultList;
	}
	
	
	/**
	 * 回复消息列表
	 * @return
	 * @throws Exception
	 * @author GUOLEI
	 * @createDate 2016-6-21
	 */
	@Override
	public NewsQueryBean findReplyNews(NewsValidCommond validCommond) throws Exception {
		NewsQueryBean newsQueryBean= null;
		if(PropertyUtil.objectNotEmpty(validCommond.getNewsId())){
			StringBuffer sql = new StringBuffer("select bu.userid \"userId\", " +
						" n.news_title \"newsTitle\"," +
						" bu.user_name \"userName\"," +
						" bb.data_name \"newsType\"," +
						" n.news_value \"newsValue\" ," +
						" bbb.att_name as \"attachName\", " +
						" bbb.att_ext as \"attachType\"," +
						" n.file_size as \"fileSize\", " +
						" n.attachment_id as \"attachmentId\"," +
						" n.class_name as \"className\"," +
						" n.news_code  as \"newsCode\",  " +
						" nul.receive_time as \"receiveTime\" " +
					" from   news  n  " +
						" left join  NEWS_USER_LINK nul  on  nul.news_id = n.news_id  " +
						" left  join  "+SystemConstants.SYSTEM_CODE+"UUM_USER  bu   on  bu.userid  = n.send_user_id  " +
						" left join BASE_BASEDATA bb  on  bb.data_code=n.news_code  " +
						" left join base_blob bbb on n.attachment_id = bbb.blob_id  " +
					" where  n.news_id=:newsId");
			Map<String, Object> values = new HashMap<String, Object>();
			
			if(PropertyUtil.objectNotEmpty(validCommond.getUserId())){
				sql.append(" and nul.user_id =:userId ");
				values.put("userId", validCommond.getUserId());
			}

			//消息ID
			values.put("newsId", validCommond.getNewsId().replace(" ", "") );
			
			
			IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
			conditionBuilder.setBaseQueryString(sql.toString());
			conditionBuilder.setQueryStringType(QueryStringType.SQL);
			conditionBuilder.setResultObject(NewsQueryBean.class);
			
	
			
			IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(values);
			List<NewsQueryBean> resultList = (List<NewsQueryBean>) this.findList(queryCondition);
			
			if(PropertyUtil.objectNotEmpty(resultList)){
				newsQueryBean=resultList.get(0);
			}
		
		}
		return newsQueryBean;
	}
	
	/**
	  * @修改接收人显示状态
	  * @Name: updateReceiveLookStata
	  * @Description: @return
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-6-24上午10:45:26
	  * @Parameters: NewsDaoImpl
	  * @Return: String
	 */
	@Override
	public String  updateReceiveLookStata(NewsValidCommond validCommond){
		String sql="update NEWS_USER_LINK set  RECEIVE_LOOK_STATE=:state ,REPLY_STATE=:rState  where  news_user_id=:newsUserId ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("state", validCommond.getLookState());
		params.put("rState", validCommond.getReplyState());
		params.put("newsUserId", validCommond.getNewsUserLink());
		this.executeUpdate(QueryStringType.SQL, sql, params);
		return null;
	}
	/**
	  * @修改发送人显示状态
	  * @Name: updateSendLookStata
	  * @Description: @return
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-6-24上午10:45:35
	  * @Parameters: NewsDaoImpl
	  * @Return: String
	 */
	@Override
	public String  updateSendLookStata(NewsValidCommond validCommond){
		String sql="update  NEWS set   SEND_LOOK_STATE=:state   where   news_id=:newsId ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("state", validCommond.getLookState());
		params.put("newsId", validCommond.getNewsId());
		this.executeUpdate(QueryStringType.SQL, sql, params);
		return null;
	}
	
	/**
	  * @修改读取状态
	  * @Name: addReply1
	  * @Description: @param validCommond
	  * @Description: @return
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-6-24下午2:07:06
	  * @Parameters: NewsDaoImpl
	  * @Return: String
	 */
	@Override
	public String  updateReadState(NewsValidCommond validCommond){
		Map<String, Object> params=new HashMap<String, Object>();
		String sql="update NEWS_USER_LINK set  READ_STATE=:state";
		if (!PropertyUtil.objectNotEmpty(validCommond.getReceiveTime())) {
			sql += ", receive_time = :receiveTime";
			params.put("receiveTime", new Date());
		}
		sql += " where   news_id=:newsId   and   user_id=:userId ";
		params.put("state", validCommond.getReadState());
		params.put("newsId", validCommond.getNewsId());
		params.put("userId", validCommond.getUserId());
		this.executeUpdate(QueryStringType.SQL, sql, params);
		return null;
	}
	
	
	
	/**
	 * 添加消息回复
	 * @throws Exception
	 * @author GUOLEI
	 * @return 
	 * @createDate 2014-5-14
	 */
	@Override
	public Boolean addReply(final NewsValidCommond validCommond)throws Exception {
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>() {
			public Boolean doInConnection(Connection conn) throws SQLException,
					DataAccessException {
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into REPLY  ");
			sql.append(" (REPLY_ID,ACTIVE_STATE,REPLY_VALUE,CREATE_DATE,user_id,NEWS_USER_ID,read_state)" );
			sql.append(" values (?,?,?,now(),?,?,?)");
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, UUID.randomUUID().toString().replace("-", ""));
			pst.setInt(2, News.IS_Y);
			pst.setString(3, validCommond.getReplyValue());
			//pst.setDate(4,  new java.sql.Date(new Date().getTime()));
			pst.setString(4, validCommond.getUserId());
			pst.setString(5, validCommond.getNewsUserLink());
			pst.setInt(6, validCommond.getReadState());
			pst.addBatch();
			pst.executeBatch();
			pst.close();
			return true;
	} catch (Exception e) {
		e.printStackTrace();
		return false;
		}
			}
	});
	}

	/**
	 * 更新回复读取状态为已读
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-6-21
	 */
	@Override
	public void updateReplyReadStata(NewsQueryCommond queryCommond) throws Exception {
		String sql="update reply set read_state = :readStateY where news_user_id = :newsUserId and user_id <> :userId";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("readStateY", News.IS_Y);
		params.put("newsUserId", queryCommond.getSearchNewsUserLink());
		params.put("userId", queryCommond.getSearchUserId());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
}
